﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Matrix.Common.DBUtility;
using Matrix.Common.Utility;
using Matrix.Common.Utility.EnumToos;
using Matrix.MaKaYunShang.Model.DataStatisticsModels;
using Matrix.MaKaYunShang.Model.Enum;

namespace Matrix.MaKaYunShang.DAL.DalExt
{
    public class GfcStatDal : BaseDAL<GfcStatDal>
    {
        #region 订单详情统计报表

        /// <summary>
        /// 订单详情统计表
        /// </summary>
        public List<T> QueryStatOrderDetailInfo<T>(bool bDisplayPage, ref int totalCount, DateTime? dtBeginTime = null,
            DateTime? dtEndTime = null, int? shopId = null, int pageIndex = 1, int pageSize = 50)
        {
            totalCount = 0;

            var sbTable = new StringBuilder();
            sbTable.Append(" OrderDetail od ");
            sbTable.Append("INNER JOIN Orders oss ON oss.Id=od.OrderId ");
            sbTable.Append("INNER JOIN Shops ss ON ss.Id=oss.ShopId ");
            sbTable.Append("LEFT JOIN ( ");
            sbTable.Append("SELECT os.Id, ISNULL(pr.WechatTransactionId, '') AS WechatTransactionId ");
            sbTable.Append("FROM  Orders os ");
            sbTable.Append("LEFT JOIN PaymentRecords pr ON os.PaySerialId=pr.Id ");
            sbTable.Append("WHERE os.IsDelete=0 ) TB1 ON TB1.Id=od.OrderId ");

            var sbQuery = new StringBuilder();
            sbQuery.Append(" CONVERT(varchar(100), oss.OrderTime, 20) AS OrderTime, oss.OrderNo, TB1.WechatTransactionId, oss.Id As OrderId, ");
            sbQuery.Append("od.Id AS OrderDetailId, od.CommodityBarCode, od.CommodityName, od.CommodityUnit, ");
            sbQuery.Append("od.CommoditySpec, od.Quantity, od.Price AS UnitPrice, ");

            sbQuery.Append(
                "CASE WHEN od.Id>(SELECT MIN(odd.Id) AS minOrderDetailId FROM OrderDetail odd WHERE odd.OrderId=oss.id) ");
            sbQuery.Append("THEN 0 ELSE oss.PayPrice END AS PayPrice, ");
            sbQuery.Append(
                "CASE WHEN od.Id>(SELECT MIN(odd.Id) AS minOrderDetailId FROM OrderDetail odd WHERE odd.OrderId=oss.id) ");
            sbQuery.Append("THEN 0 ELSE oss.RandomPayDiscount END AS RandomPayDiscount, ");
            sbQuery.Append("dbo.[fn_GetPayWayDesc](oss.PayWay, oss.PayPrice, oss.Id, " +
                               (int)CommonEnum.OrderPayWay.WeChat + ", '" +
                               CommonEnum.OrderPayWay.WeChat.GetDescription() + "', ");
            sbQuery.Append((int)CommonEnum.OrderPayWay.Ticket + ", '" +
                           CommonEnum.OrderPayWay.Ticket.GetDescription() + "', ");
            sbQuery.Append("'" + CommonEnum.OrderPayWay.Cash.GetDescription() + "', '" +
                           CommonEnum.OrderPayWay.ETicket.GetDescription() + "') AS PayWayDesc, ");

            sbQuery.Append("ss.Name AS ShopName, CASE od.IsGift WHEN 1 THEN '赠送' ELSE '' END AS SellingWay ");

            var sbWhere = new StringBuilder();
            sbWhere.Append(" oss.OrderStatus<=" + (int)CommonEnum.OrderStatus.Finished +
                           " AND oss.IsDelete=0 AND od.IsDelete=0 ");

            var sbOrderBy = new StringBuilder();
            sbOrderBy.Append(" od.Id, od.IsGift ");

            var lsParameters = new List<SqlParameter>();
            if (dtBeginTime != null)
            {
                sbWhere.Append(" AND oss.OrderTime>=@BeginTime ");
                lsParameters.Add(new SqlParameter("@BeginTime", (DateTime)dtBeginTime));
            }
            if (dtEndTime != null)
            {
                sbWhere.Append(" AND oss.OrderTime<@EndTime ");
                lsParameters.Add(new SqlParameter("@EndTime", (DateTime)dtEndTime));
            }
            if (shopId != null)
            {
                sbWhere.Append(" AND oss.ShopId=@ShopId ");
                lsParameters.Add(new SqlParameter("@ShopId", (int)shopId));
            }

            if (bDisplayPage)
            {
                return GetSimpleListPageExpand<T>(pageIndex, pageSize, sbTable.ToString(),
                sbQuery.ToString(), sbOrderBy.ToString(), sbWhere.ToString(), ref totalCount,
                lsParameters.Count > 0 ? lsParameters.ToArray() : null);
            }

            var sbFullSql = new StringBuilder();
            sbFullSql.Append("SELECT " + sbQuery);
            sbFullSql.Append(" FROM " + sbTable);
            sbFullSql.Append(" WHERE " + sbWhere);
            sbFullSql.Append("ORDER BY " + sbOrderBy);

            var lsRet = DbHelperSQL.ExecuteSqlList<T>(sbFullSql.ToString(), lsParameters.ToArray());
            return lsRet != null && lsRet.Count > 0 ? lsRet.ToList() : null;

        }
        #endregion

        #region 店铺商品统计表报

        /// <summary>
        /// 店铺商品统计报表
        /// </summary>
        /// <param name="bDisplayPage"></param>
        /// <param name="queryDateType"></param>
        /// <param name="totalCount"></param>
        /// <param name="isGroupbyShop"></param>
        /// <param name="isGroupByCommodity"></param>
        /// <param name="isGroupByPayWay"></param>
        /// <param name="isGroupByDeliveryAccount"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="dtBeginTime"></param>
        /// <param name="dtEndTime"></param>
        /// <param name="shopId"></param>
        /// <param name="orderPayWay"></param>
        /// <param name="commodityName"></param>
        /// <param name="commodityBarCode"></param>
        /// <param name="deliveryAccountId"></param>
        /// <param name="isGroupByConsumer"></param>
        /// <param name="consumerName"></param>
        /// <returns></returns>
        public List<T> QueryStatShopCommoditySellingInfo<T>(bool bDisplayPage,
            CommonEnum.DataStatQueryDateType queryDateType, ref int totalCount, bool isGroupbyShop = false,
            bool isGroupByCommodity = false, bool isGroupByPayWay = false, bool isGroupByConsumer = false,
            bool isGroupByDeliveryAccount = false, DateTime? dtBeginTime = null, DateTime? dtEndTime = null, int? shopId = null,
            int? orderPayWay = null, string commodityName = "", string commodityBarCode = "",
            int? deliveryAccountId = null, string consumerName = null, int pageIndex = 1, int pageSize = 50)
        {
            var sbTable = new StringBuilder();
            sbTable.Append(" OrderDetail od ");
            sbTable.Append("INNER JOIN Orders os ON os.Id=od.OrderId ");
            if (isGroupbyShop)
            {
                sbTable.Append("INNER JOIN Shops ss ON ss.Id=os.ShopId ");
            }
            if (isGroupByDeliveryAccount)
            {
                sbTable.Append("INNER JOIN McAccounts ma ON ma.Id=os.DeliveryAccountId ");
            }

            var sbGroupBy = new StringBuilder();
            sbGroupBy.Append("GROUP BY od.IsGift");
            if (isGroupbyShop)
            {
                sbGroupBy.Append(", ss.Name, os.ShopId");
            }
            if (isGroupByCommodity)
            {
                sbGroupBy.Append(", od.CommodityName, od.CommodityBarCode, od.Price");
            }
            if (isGroupByConsumer)
            {
                sbGroupBy.Append(", os.ConsumerId, os.ConsumerName, os.ReceiverName");
            }
            if (isGroupByDeliveryAccount)
            {
                sbGroupBy.Append(", os.DeliveryAccountId,DisplayName");
            }

            var sbOrderBy = new StringBuilder();
            sbOrderBy.Append(" ORDER BY ");
            if (isGroupbyShop)
            {
                sbOrderBy.Append("os.ShopId, ");
            }
            if (isGroupByConsumer)
            {
                sbOrderBy.Append("os.ConsumerId, ");
            }
            if (isGroupByDeliveryAccount)
            {
                sbOrderBy.Append("os.DeliveryAccountId, ");
            }
            if (isGroupByCommodity)
            {
                sbOrderBy.Append("od.CommodityName, ");
            }

            var sbQuery = new StringBuilder();
            sbQuery.Append(
                "SUM(od.Quantity) AS CommoditySoldCount, SUM(od.Quantity*od.price) AS TotalIncome,  ");

            sbQuery.Append("CASE od.IsGift WHEN 1 THEN '赠送' ELSE '' END AS SellingWay ");

            var sbPayWayFunction = new StringBuilder();

            if (isGroupByPayWay || orderPayWay != null)
            {
                sbPayWayFunction.Append(" dbo.[fn_GetPayWayDesc](os.PayWay, os.PayPrice, os.Id, " +
                               (int)CommonEnum.OrderPayWay.WeChat + ", '" +
                               CommonEnum.OrderPayWay.WeChat.GetDescription() + "', ");

                sbPayWayFunction.Append((int)CommonEnum.OrderPayWay.Ticket + ", '" +
                               CommonEnum.OrderPayWay.Ticket.GetDescription() + "', ");

                sbPayWayFunction.Append("'" + CommonEnum.OrderPayWay.Cash.GetDescription() + "', '" +
                               CommonEnum.OrderPayWay.ETicket.GetDescription() + "') ");

                sbQuery.Append(", " + sbPayWayFunction + " AS PayWayDesc ");
                sbGroupBy.Append(", " + sbPayWayFunction);
            }
            else
            {
                sbQuery.Append(", '全部' AS PayWayDesc ");
            }

            if (isGroupbyShop)
            {
                sbQuery.Append(", ss.Name As ShopName, os.ShopId As ShopId ");
            }
            else
            {
                if (shopId != null)
                {
                    var shopInfo = ShopsDAL.Instance.GetModelById((int)shopId);
                    var strShopName = shopInfo == null || shopInfo.Id == 0 ? "未知店铺" : shopInfo.Name;
                    sbQuery.Append(", '" + strShopName + "' As ShopName ");
                }
                else
                {
                    sbQuery.Append(", '全部' As ShopName ");
                }
            }

            //sbQuery.Append(isGroupbyShop ? ", ss.Name As ShopName, ss.Id As ShopId " : ", '全部' As ShopName");

            switch (queryDateType)
            {
                case CommonEnum.DataStatQueryDateType.DayStat:
                    sbQuery.Append(", CONVERT(nvarchar(50), os.OrderTime, 23) AS OrderDate ");
                    sbGroupBy.Append(", CONVERT(nvarchar(50), os.OrderTime, 23) ");
                    sbOrderBy.Append(" CONVERT(nvarchar(50), os.OrderTime, 23) ");
                    break;
                case CommonEnum.DataStatQueryDateType.WeekStat:
                    sbQuery.Append(", fn_WeekBeginEndDay(os.OrderTime) AS OrderDate ");
                    sbGroupBy.Append(", fn_WeekBeginEndDay(os.OrderTime) ");
                    sbOrderBy.Append(" fn_WeekBeginEndDay(os.OrderTime) ");
                    break;
                case CommonEnum.DataStatQueryDateType.MonthStat:
                    sbQuery.Append(", CONVERT(NVARCHAR(10),DATEADD(d,-DAY(os.OrderTime)+1,os.OrderTime), 23) ");
                    sbQuery.Append("+' - '+ ");
                    sbQuery.Append("CONVERT(NVARCHAR(10),DATEADD(d,-DAY(os.OrderTime),DATEADD(m,1,os.OrderTime)),23) ");
                    sbQuery.Append("AS OrderDate ");

                    sbGroupBy.Append(", CONVERT(NVARCHAR(10),DATEADD(d,-DAY(os.OrderTime)+1,os.OrderTime), 23) ");
                    sbGroupBy.Append("+' - '+ ");
                    sbGroupBy.Append("CONVERT(NVARCHAR(10),DATEADD(d,-DAY(os.OrderTime),DATEADD(m,1,os.OrderTime)),23) ");

                    sbOrderBy.Append(" CONVERT(NVARCHAR(10),DATEADD(d,-DAY(os.OrderTime)+1,os.OrderTime), 23) ");
                    sbOrderBy.Append("+' - '+ ");
                    sbOrderBy.Append("CONVERT(NVARCHAR(10),DATEADD(d,-DAY(os.OrderTime),DATEADD(m,1,os.OrderTime)),23) ");
                    break;
                case CommonEnum.DataStatQueryDateType.YearStat:
                    sbQuery.Append(",cast(year(os.OrderTime) as varchar(20)) as OrderDate  ");
                    sbGroupBy.Append(",  year(os.OrderTime) ");
                    sbOrderBy.Append(" year(os.OrderTime) ");
                    break;
            }

            if (isGroupByConsumer)
            {
                sbQuery.Append(", os.ConsumerName, os.ConsumerId, os.ReceiverName");
            }
            else
            {
                sbQuery.Append(", '全部' AS ConsumerName ,0 AS ConsumerId, '' AS ReceiverName");
            }
            if (isGroupByDeliveryAccount)
            {
                sbQuery.Append(", os.DeliveryAccountId, DisplayName");
            }
            else
            {
                sbQuery.Append(", '全部' AS DisplayName");
            }

            sbQuery.Append(isGroupByCommodity
                ? ", od.CommodityName, od.CommodityBarCode, od.Price AS UnitPrice "
                : ", '全部' AS CommodityName, '' AS CommodityBarCode, 0.00 AS UnitPrice ");



            var sbWhere = new StringBuilder();
            sbWhere.Append(" os.OrderStatus<=" + (int)CommonEnum.OrderStatus.Finished +
                           " AND os.IsDelete=0 AND od.IsDelete=0 ");

            var lsParameters = new List<SqlParameter>();
            if (dtBeginTime != null)
            {
                sbWhere.Append(" AND os.OrderTime>=@BeginTime ");
                lsParameters.Add(new SqlParameter("@BeginTime", (DateTime)dtBeginTime));
            }
            if (dtEndTime != null)
            {
                sbWhere.Append(" AND os.OrderTime<@EndTime ");
                lsParameters.Add(new SqlParameter("@EndTime", (DateTime)dtEndTime));
            }
            if (shopId != null)
            {
                sbWhere.Append(" AND os.ShopId=@ShopId ");
                lsParameters.Add(new SqlParameter("@ShopId", (int)shopId));
            }
            if (orderPayWay != null)
            {
                if ((int)orderPayWay == (int)CommonEnum.OrderPayWay.ETicket)
                {
                    sbWhere.Append(" AND " + sbPayWayFunction + " LIKE '%" + CommonEnum.OrderPayWay.ETicket.GetDescription() + "' ");
                }
                else
                {
                    sbWhere.Append(" AND os.PayWay=@PayWay ");
                    lsParameters.Add(new SqlParameter("@PayWay", (int)orderPayWay));
                }
            }
            if (!string.IsNullOrWhiteSpace(commodityName))
            {
                sbWhere.Append(" AND od.CommodityName=@CommodityName ");
                lsParameters.Add(new SqlParameter("@CommodityName", commodityName));
            }
            if (!string.IsNullOrWhiteSpace(commodityBarCode))
            {
                sbWhere.Append(" AND od.CommodityBarCode=@CommodityBarCode ");
                lsParameters.Add(new SqlParameter("@CommodityBarCode", commodityBarCode));
            }
            if (deliveryAccountId != null)
            {
                sbWhere.Append(" AND os.DeliveryAccountId=@DeliveryAccountId ");
                lsParameters.Add(new SqlParameter("@DeliveryAccountId", (int)deliveryAccountId));
            }
            if (!string.IsNullOrEmpty(consumerName))
            {
                sbWhere.Append(" AND (os.ConsumerName=@ConsumerName OR os.ReceiverName=@ConsumerName) ");
                lsParameters.Add(new SqlParameter("@ConsumerName", consumerName));
            }

            var sbSql = new StringBuilder();
            if (bDisplayPage)
            {
                sbSql.Append("SELECT COUNT(1) totalcount FROM ");
                sbSql.Append("( SELECT " + sbQuery + " FROM " + sbTable + " ");
                sbSql.Append("WHERE " + sbWhere + " " + sbGroupBy + ") AS CountTable; ");

                sbSql.Append("SELECT * FROM (");
                sbSql.Append("SELECT " + sbQuery + ",ROW_NUMBER() OVER(" + sbOrderBy +
                             ") AS RowRank ");
                sbSql.Append("FROM " + sbTable + " ");
                sbSql.Append("WHERE " + sbWhere + " " + sbGroupBy + " ");

                sbSql.Append(") AS TEMP WHERE RowRank Between " + ((pageIndex - 1) * pageSize + 1) + " AND " +
                             pageIndex * pageSize);

                var ds = new DataSet();
                if (lsParameters.Count > 0)
                {
                    ds = DbHelperSQL.Query(sbSql.ToString(), lsParameters.ToArray());
                }
                else
                {
                    ds = DbHelperSQL.Query(sbSql.ToString());
                }
                totalCount = int.Parse(ds.Tables[0].Rows[0]["totalcount"].ToString());
                return (List<T>)ServicesHelper.DataSetToIList<T>(ds, 1);
            }

            var sbFullSql = new StringBuilder();
            sbFullSql.Append("SELECT " + sbQuery);
            sbFullSql.Append(" FROM " + sbTable);
            sbFullSql.Append(" WHERE " + sbWhere);
            sbFullSql.Append(sbGroupBy);
            sbFullSql.Append(sbOrderBy);

            var lsRet = DbHelperSQL.ExecuteSqlList<T>(sbFullSql.ToString(), lsParameters.ToArray());
            return lsRet != null && lsRet.Count > 0 ? lsRet.ToList() : null;
        }

        #endregion

        #region 订单统计报表

        /// <summary>
        /// 获取订单统计报表
        /// </summary>
        /// <param name="bDisplayPage"></param>
        /// <param name="totalCount"></param>
        /// <param name="queryDateType"></param>
        /// <param name="isGroupbyShop"></param>
        /// <param name="isGroupByPayWay"></param>
        /// <param name="isGroupByOrderStatus"></param>
        /// <param name="dtBeginTime"></param>
        /// <param name="dtEndTime"></param>
        /// <param name="shopId"></param>
        /// <param name="orderPayWay"></param>
        /// <param name="orderStatus"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public List<DataStatShopOrders> QueryStatShopOrders(bool bDisplayPage, CommonEnum.DataStatQueryDateType queryDateType,
            ref int totalCount, bool isGroupbyShop = false, bool isGroupByPayWay = false,
            bool isGroupByOrderStatus = false, DateTime? dtBeginTime = null, DateTime? dtEndTime = null,
            int? shopId = null, int? orderPayWay = null, int? orderStatus = null, int pageIndex = 1, int pageSize = 50)
        {
            totalCount = 0;

            var sbTable = new StringBuilder();
            sbTable.Append(" Orders os ");
            if (isGroupbyShop)
            {
                sbTable.Append("INNER JOIN Shops ss ON ss.Id=os.ShopId ");
            }

            var sbGroupBy = new StringBuilder();
            sbGroupBy.Append("GROUP BY ");

            var sbOrderBy = new StringBuilder();
            sbOrderBy.Append(" ORDER BY ");
            if (isGroupbyShop)
            {
                sbOrderBy.Append(" os.ShopId, ");
            }

            var sbQuery = new StringBuilder();
            sbQuery.Append("COUNT(os.Id) AS OrderAmount, SUM(os.DiscountPrice) AS TotalPrice ");

            switch (queryDateType)
            {
                case CommonEnum.DataStatQueryDateType.DayStat:
                    sbQuery.Append(" ,CONVERT(nvarchar(50), os.OrderTime, 23) AS OrderDate ");
                    sbGroupBy.Append("CONVERT(nvarchar(50), os.OrderTime, 23) ");
                    sbOrderBy.Append(" CONVERT(nvarchar(50), os.OrderTime, 23) ");
                    break;
                case CommonEnum.DataStatQueryDateType.WeekStat:
                    sbQuery.Append(", dbo.fn_WeekBeginEndDay(os.OrderTime) AS OrderDate ");
                    sbGroupBy.Append(" dbo.fn_WeekBeginEndDay(os.OrderTime) ");
                    sbOrderBy.Append("dbo.fn_WeekBeginEndDay(os.OrderTime) ");
                    break;
                case CommonEnum.DataStatQueryDateType.MonthStat:
                    sbQuery.Append(", CONVERT(NVARCHAR(10),DATEADD(d,-DAY(os.OrderTime)+1,os.OrderTime), 23) ");
                    sbQuery.Append("+' - '+ ");
                    sbQuery.Append("CONVERT(NVARCHAR(10),DATEADD(d,-DAY(os.OrderTime),DATEADD(m,1,os.OrderTime)),23) ");
                    sbQuery.Append("AS OrderDate ");

                    sbOrderBy.Append(" CONVERT(NVARCHAR(10),DATEADD(d,-DAY(os.OrderTime)+1,os.OrderTime), 23) ");
                    sbOrderBy.Append("+' - '+ ");
                    sbOrderBy.Append("CONVERT(NVARCHAR(10),DATEADD(d,-DAY(os.OrderTime),DATEADD(m,1,os.OrderTime)),23) ");

                    sbGroupBy.Append("CONVERT(NVARCHAR(10),DATEADD(d,-DAY(os.OrderTime)+1,os.OrderTime), 23) ");
                    sbGroupBy.Append("+' - '+ ");
                    sbGroupBy.Append("CONVERT(NVARCHAR(10),DATEADD(d,-DAY(os.OrderTime),DATEADD(m,1,os.OrderTime)),23) ");
                    break;
                case CommonEnum.DataStatQueryDateType.YearStat:
                    sbQuery.Append(",cast(year(os.OrderTime) as varchar(20)) as OrderDate  ");
                    sbOrderBy.Append("  year(os.OrderTime) ");
                    sbGroupBy.Append("  year(os.OrderTime) ");
                    break;
            }

            if (isGroupbyShop)
            {
                sbQuery.Append(", os.ShopId, ss.Name AS ShopName ");
                sbGroupBy.Append(", os.ShopId, ss.Name ");
            }
            else
            {
                if (shopId != null)
                {
                    var shopInfo = ShopsDAL.Instance.GetModelById((int)shopId);
                    var strShopName = shopInfo == null || shopInfo.Id == 0 ? "未知店铺" : shopInfo.Name;
                    sbQuery.Append(", '" + strShopName + "' As ShopName ");
                }
                else
                {
                    sbQuery.Append(", '全部' As ShopName ");
                }
            }

            var sbPayWayFunction = new StringBuilder();

            if (isGroupByPayWay || orderPayWay != null)
            {
                sbPayWayFunction.Append(" dbo.[fn_GetPayWayDesc](os.PayWay, os.PayPrice, os.Id, " +
                               (int)CommonEnum.OrderPayWay.WeChat + ", '" +
                               CommonEnum.OrderPayWay.WeChat.GetDescription() + "', ");

                sbPayWayFunction.Append((int)CommonEnum.OrderPayWay.Ticket + ", '" +
                               CommonEnum.OrderPayWay.Ticket.GetDescription() + "', ");

                sbPayWayFunction.Append("'" + CommonEnum.OrderPayWay.Cash.GetDescription() + "', '" +
                               CommonEnum.OrderPayWay.ETicket.GetDescription() + "') ");

                sbQuery.Append(", " + sbPayWayFunction + " AS PayWayDesc ");
                sbGroupBy.Append(", " + sbPayWayFunction);
            }
            else
            {
                sbQuery.Append(", '全部' AS PayWayDesc ");
            }

            if (isGroupByOrderStatus)
            {
                sbQuery.Append(", os.OrderStatus ");
                sbGroupBy.Append(", os.OrderStatus ");
            }
            else
            {
                sbQuery.Append(", -1 AS OrderStatus ");
            }

            var sbWhere = new StringBuilder();
            sbWhere.Append(" os.IsDelete=0 ");

            var lsParameters = new List<SqlParameter>();
            if (dtBeginTime != null)
            {
                sbWhere.Append(" AND os.OrderTime>=@BeginTime ");
                lsParameters.Add(new SqlParameter("@BeginTime", (DateTime)dtBeginTime));
            }
            if (dtEndTime != null)
            {
                sbWhere.Append(" AND os.OrderTime<@EndTime ");
                lsParameters.Add(new SqlParameter("@EndTime", (DateTime)dtEndTime));
            }
            if (shopId != null)
            {
                sbWhere.Append(" AND os.ShopId=@ShopId ");
                lsParameters.Add(new SqlParameter("@ShopId", (int)shopId));
            }
            if (orderPayWay != null)
            {
                if ((int)orderPayWay == (int)CommonEnum.OrderPayWay.ETicket)
                {
                    sbWhere.Append(" AND " + sbPayWayFunction + " LIKE '%" + CommonEnum.OrderPayWay.ETicket.GetDescription() + "' ");
                }
                else
                {
                    sbWhere.Append(" AND os.PayWay=@PayWay ");
                    lsParameters.Add(new SqlParameter("@PayWay", (int)orderPayWay));
                }
            }
            if (orderStatus != null)
            {
                sbWhere.Append("and orderstatus=" + orderStatus);
            }
            if (bDisplayPage)
            {
                var sbSql = new StringBuilder();
                sbSql.Append("SELECT COUNT(1) totalcount FROM ");
                sbSql.Append("( SELECT " + sbQuery + " FROM " + sbTable + " ");
                sbSql.Append("WHERE " + sbWhere + " " + sbGroupBy + ") AS CountTable; ");

                sbSql.Append("SELECT * FROM (");
                sbSql.Append("SELECT " + sbQuery + ",ROW_NUMBER() OVER(" + sbOrderBy +
                             ") AS RowRank ");
                sbSql.Append("FROM " + sbTable + " ");
                sbSql.Append("WHERE " + sbWhere + " " + sbGroupBy + " ");

                sbSql.Append(") AS TEMP WHERE RowRank Between " + ((pageIndex - 1) * pageSize + 1) + " AND " +
                             pageIndex * pageSize);

                var ds = new DataSet();
                if (lsParameters.Count > 0)
                {
                    ds = DbHelperSQL.Query(sbSql.ToString(), lsParameters.ToArray());
                }
                else
                {
                    ds = DbHelperSQL.Query(sbSql.ToString());
                }
                totalCount = int.Parse(ds.Tables[0].Rows[0]["totalcount"].ToString());
                return (List<DataStatShopOrders>)ServicesHelper.DataSetToIList<DataStatShopOrders>(ds, 1);
            }

            var sbFullSql = new StringBuilder();
            sbFullSql.Append("SELECT " + sbQuery);
            sbFullSql.Append(" FROM " + sbTable);
            sbFullSql.Append(" WHERE " + sbWhere);
            sbFullSql.Append("ORDER BY " + sbOrderBy);

            var lsRet = DbHelperSQL.ExecuteSqlList<DataStatShopOrders>(sbFullSql.ToString(), lsParameters.ToArray());
            return lsRet != null && lsRet.Count > 0 ? lsRet.ToList() : null;
        }
        #endregion


    }
}
